﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL;
using KPIS.GERP.GHRMS.DALFactory;
using KPIS.DBM;


namespace KPIS.GERP.GHRMS.BLL
{
    public class PayrollsBLL : IPayrollsBLL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataSet ListPayrolls(int emp_type_seq, int pay_year, int pay_month)
        {
            dbManager.CreateParameters(4);
            dbManager.AddParameters(0, "@emp_type_seq", emp_type_seq);
            dbManager.AddParameters(1, "@pay_year", pay_year);
            dbManager.AddParameters(2, "@pay_month", pay_month);
            dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrm_pr_payrolls  where emp_type_seq=@emp_type_seq and pay_year=@pay_year and pay_month=@pay_month and record_status=@record_status");
            dbManager.Dispose();

            return ds;
        }

        public DataSet ListPayrollsEmp()
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);


            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select *,PD.salary as pd_salary, PE.salary as pe_salary, PD.emp_seq as pd_emp_seq from hrm_pr_payrolls as PP,  hrm_pr_payroll_dtls as PD, hrm_ps_employees as PE, hrm_mp_positions as MP where PP.pay_seq=PD.pay_seq and  PD.emp_seq = PE.emp_seq and PE.pos_seq = MP.pos_seq and PD.record_status = @record_status");
            dbManager.Dispose();

            return ds;
        }

        public bool ChkDuplicatePayrollsDtls(int pay_seq, int emp_seq)
        {
            dbManager.CreateParameters(3);
            dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@pay_seq", pay_seq);
            dbManager.AddParameters(2, "@emp_seq", emp_seq);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrm_pr_payroll_dtls where record_status = @record_status and pay_seq=@pay_seq and emp_seq=@emp_seq");
            if (ds.Tables[0].Rows.Count != 0)
                return true;
            else
                return false;
        }

        public DataSet ListPayrollsEmpType(int emp_type_seq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@emp_type_seq", emp_type_seq);
            dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select *,PD.salary as pd_salary, PE.salary as pe_salary, PD.emp_seq as pd_emp_seq, PD.remarks as pd_remarks from hrm_pr_payrolls as PP,  hrm_pr_payroll_dtls as PD, hrm_ps_employees as PE, hrm_mp_positions as MP where PP.pay_seq=PD.pay_seq and  PD.emp_seq = PE.emp_seq and PE.pos_seq = MP.pos_seq and PP.emp_type_seq=@emp_type_seq and PD.record_status = @record_status");
            dbManager.Dispose();
            return ds;
        }

        public DataSet ListPayrollsEmpTypeDate(int emp_type_seq, int pay_month, int pay_year)
        {
            dbManager.CreateParameters(4);
            dbManager.AddParameters(0, "@emp_type_seq", emp_type_seq);
            dbManager.AddParameters(1, "@pay_month", pay_month);
            dbManager.AddParameters(2, "@pay_year", pay_year);
            dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string strSQL = "select PP.*, PD.*"
                + ", CONCAT(NP.prefix_name, PE.first_name,' ',PE.last_name) full_name,PD.amount as pd_salary"
                + ", PE.salary as pe_salary, PD.emp_seq as pd_emp_seq, isnull(PD.remarks, '') as pd_remarks "
                + ", ISNULL(SP.sum_ext, 0) AS sum_ext, MP.pos_ref, MP.pos_name, MC.class_name, MP.job_site_seq, JS.job_Site_name"
                + " from hrm_pr_payrolls as PP"
                    + " left join hrm_pr_payroll_dtls as PD on PP.pay_seq = PD.pay_seq"
                    + " left join hrm_ps_employees as PE on PD.emp_seq = PE.emp_seq"
                    + " left join hrm_mp_positions as MP on PE.pos_seq = MP.pos_seq"
                    + " left join mas_name_prefixes as NP on PE.name_prefix_seq = NP.name_prefix_seq"
                    + " left join hrm_mp_classes as MC on PE.class_seq = MC.class_seq"
                    + " left join hrm_mp_job_sites as JS on MP.job_site_seq = JS.job_site_seq"
                    + " left join (select sum(amount) AS sum_ext, d.emp_seq, p.pay_seq"
                        + " from hrm_pr_payroll_dtls d"
                        + " left join hrm_pr_payrolls p on d.pay_seq = p.pay_seq"
                        + " where d.record_status = @record_status"
                        + " and d.ext_seq not like 99999"// Fix เงินเดือน
                        + " group by d.emp_seq, p.pay_seq)SP on PP.pay_seq = SP.pay_seq and PD.emp_seq = SP.emp_seq "
                + " where PD.record_status =@record_status "
                    + " and PP.emp_type_seq=@emp_type_seq"
                    + " and PP.pay_month=@pay_month"
                    + " and PP.pay_year=@pay_year"
                    + " and PD.ext_seq = 99999";//เอาเฉพาะเงินเดือน
            ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            dbManager.Dispose();
            return ds;
        }

        public void DeleteEmpPayroll(int Sequence, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_pr_payroll_dtls SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE pay_dtls_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public DataSet ListPayrollsExtraTyp()
        { 
         dbManager.CreateParameters(1);
         dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text
                , "SELECT * FROM HRM_PR_EXTRA_TYPES"
                    + " WHERE RECORD_STATUS = @record_status");
            dbManager.Dispose();
            return ds;
        }

        public DataSet ListExtraTypeNotExist(int EmployeeType, int PayRollSeq, int EmpSeq)
        { 
             dbManager.CreateParameters(4);
             dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);
             dbManager.AddParameters(1, "@emp_type_seq", EmployeeType);
             dbManager.AddParameters(2, "@pay_seq", PayRollSeq);
             dbManager.AddParameters(3, "@emp_seq", EmpSeq);
            
            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text
                , "SELECT * FROM HRM_PR_EXTRA_TYPES"
                    + " WHERE RECORD_STATUS = @record_status"
                    + " AND EMP_TYPE_SEQ = @emp_type_seq"
                    + " AND EXT_SEQ NOT IN (select ext_seq from hrm_pr_payroll_dtls"
                        + " where record_status = @record_status"
                        + " and pay_seq = @pay_seq"
                        + " and emp_seq = @emp_seq)"
                );
            dbManager.Dispose();
            return ds;
        }


        public bool ChkDuplicatePayrolls(int emp_type_seq, int pay_year, int pay_month)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrm_pr_payrolls where emp_type_seq=" + emp_type_seq + " and record_status = '" + SystemInfo._ActiveRecordStatus + "' and pay_year=" + pay_year + " and pay_month=" + pay_month);
            if (ds.Tables[0].Rows.Count != 0)
                return true;
            else
                return false;
        }

        public void InsertPayrolls(int emp_type_seq, int pay_year, int pay_month, string CreatedBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(1, "@pay_year", pay_year);
                dbManager.AddParameters(2, "@pay_month", pay_month);

                dbManager.AddParameters(3, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(4, "@CreatedWhen", DateTime.Today);
                dbManager.AddParameters(5, "@record_status", SystemInfo._ActiveRecordStatus);

                dbManager.ExecuteNonQuery(CommandType.Text
                    , "INSERT INTO HRM_PR_PAYROLLS"
                    + " (EMP_TYPE_SEQ,PAY_YEAR,PAY_MONTH,CREATED_BY,CREATED_WHEN,RECORD_STATUS)"
                    + " VALUES (@emp_type_seq, @pay_year, @pay_month, @CreatedBy,@CreatedWhen,@record_status)");
                
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public void Insert(int pay_seq, int emp_seq, Double salary, string remarks, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@pay_seq", pay_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@salary", salary);

                dbManager.AddParameters(3, "@remarks", remarks);
                dbManager.AddParameters(4, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(5, "@CreatedWhen", DateTime.Today);
                dbManager.AddParameters(6, "@record_status", SystemInfo._ActiveRecordStatus);

                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "INSERT INTO hrm_pr_payroll_dtls (pay_seq, emp_seq, amount, remarks, created_by, created_when, record_status) VALUES (@pay_seq, @emp_seq, @salary, @remarks, @CreatedBy,@CreatedWhen,@record_status)");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        public void InsertSelect(int emp_type_seq, int bg_year, int month, int pay_seq, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(1, "@bg_year", bg_year);
                dbManager.AddParameters(2, "@month", month);
                dbManager.AddParameters(3, "@pay_seq", pay_seq);
                dbManager.AddParameters(4, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(5, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(6, "@CreatedWhen", DateTime.Today);
                dbManager.AddParameters(7, "@date_work_start", DateTime.Parse("01/" + month + "/" + bg_year));// (bg_year + 543).ToString + "/" + month < 10 ? "0" + month.ToString() : month.ToString() + "/01");

                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text
                    , "DELETE FROM HRM_PR_PAYROLL_DTLS WHERE PAY_SEQ = @pay_seq");
                dbManager.ExecuteNonQuery(CommandType.Text
                    , "INSERT INTO HRM_PR_PAYROLL_DTLS"
                        + "(PAY_SEQ, EMP_SEQ, EXT_SEQ, AMOUNT, AMOUNT_PAY, JOB_SITE_SEQ, REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                        + " select @pay_seq as pay_seq"
                            + ", e.emp_seq, 99999, e.salary, e.salary"
                            + ", case when j.gfmis_code is null then j.upper_job_site_seq else e.working_job_site_seq end as job_site_seq"
                            + ", null as remarks, @record_status as record_status"
                            + ", @CreatedBy as created_by, @CreatedWhen as created_when"
                        + " from hrm_ps_employees e"
                        + " left join hrm_mp_job_sites j on e.working_job_site_seq = j.job_site_seq"
                        + " where e.emp_type_seq = @emp_type_seq"
                            + " and e.date_admitted <= @date_work_start"
                            + " and (e.date_quit >= @date_work_start or e.date_quit is null)"
                            + " and e.record_status = @record_status"
                     );
                dbManager.ExecuteNonQuery(CommandType.Text
                    , "INSERT INTO HRM_PR_PAYROLL_DTLS"
                    + "(PAY_SEQ, EMP_SEQ, EXT_SEQ, AMOUNT, AMOUNT_PAY, JOB_SITE_SEQ, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " select @pay_seq as pay_seq, d.emp_seq, d.ext_seq, d.amount, d.amount_pay"
                    + ", d.job_site_seq"
                    + ", d.record_status, @CreatedBy as created_by, @CreatedWhen as created_when"
                    + " from hrm_pr_payroll_dtls d"
                    + " inner join hrm_pr_payrolls p on d.pay_seq = p.pay_seq"
                    + " left join hrm_pr_extra_types ex on d.ext_seq = ex.ext_seq"
                    + " where p.emp_type_seq = @emp_type_seq"
                        + " and case when @month = 1 then p.pay_year = @bg_year - 1 and p.pay_month = 12"
                            + " else p.pay_year = @bg_year and p.pay_month = @month - 1 end"
                        + " and ex.ext_monthly = 'Y'"
                        + " and d.ext_seq not like 99999"
                        + " and d.record_status = @record_status"
                    );
               
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(int Sequence, Double salary, string remarks, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@sequence", Sequence);

                dbManager.AddParameters(1, "@salary", salary);
                dbManager.AddParameters(2, "@remarks", remarks);
                dbManager.AddParameters(3, "@updated_by", UpdateBy);
                dbManager.AddParameters(4, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_pr_payroll_dtls SET amount=@salary, remarks=@remarks, updated_by=@updated_by,updated_when=@updated_when WHERE pay_dtls_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        public void Delete(int Sequence, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE HRM_PR_EXTRA_TYPES SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE ext_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        public DataTable RPT_PayrollSlips(int EmpSequence, string pMonth, string pYear)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(1, "@Month", pMonth);
                dbManager.AddParameters(2, "@Year", pYear);

                strSQL = " SELECT DISTINCT payrollD.emp_seq"
                        + ", concat(prefitx.prefix_name,Emp.first_name, ' ',Emp.last_name) as full_name "
                        + ", payroll.pay_year,payroll.pay_month,Emp.salary,Emp.pos_seq,payrollD.amount "
                        + ", payrollD.amount_pay,hrm_pr_extra_types.ext_code,hrm_pr_extra_types.ext_name "
                        + ", hrm_pr_extra_types.ext_name_short,hrm_pr_extra_types.ext_flag "
                        + ", p.pos_name,p.pos_ref, wj.job_site_name as working_job_site_name "
                        + ", bk_acc.bank_acc_no, concat(acc.bank_name , ' ', bb.branch_name) as bank_name"
                        + ", CASE payroll.pay_month WHEN 1 THEN 'มกราคม'"
                            + " WHEN 2 THEN 'กุมภาพันธ์'"
                            + " WHEN 3 THEN 'มีนาคม'"
                            + " WHEN 4 THEN 'เมษายน'"
                            + " WHEN 5 THEN 'พฤษภาคม'"
                            + " WHEN 6 THEN 'มิถุนายน'"
                            + " WHEN 7 THEN 'กรกฎาคม'"
                            + " WHEN 8 THEN 'สิงหาคม'"
                            + " WHEN 9 THEN 'กันยายน'"
                            + " WHEN 10 THEN 'ตุลาคม'"
                            + " WHEN 11 THEN 'พฤศจิกายน'"
                            + " WHEN 12 THEN 'ธันวาคม'"
                            + " ELSE '' END AS month_name"
                        + ", (select sum(amount_pay) from hrm_pr_payroll_dtls d "
                            + " inner join hrm_pr_payrolls p on d.pay_seq = p.pay_seq"
                            + " left join hrm_pr_extra_types et on d.ext_seq = et.ext_seq"
                            + " where p.pay_year = @Year"
                                + " and p.pay_month = @Month"
                                + " and ext_flag = 'I'" // เงินได้
                                + " and d.emp_seq = @EmpSequence"
                                + " group by d.emp_seq ) as sum_income"
                        + ", (select sum(amount_pay) from hrm_pr_payroll_dtls d "
                            + " inner join hrm_pr_payrolls p on d.pay_seq = p.pay_seq"
                            + " left join hrm_pr_extra_types et on d.ext_seq = et.ext_seq"
                            + " where p.pay_year = @Year"
                                + " and p.pay_month = @Month"
                                + " and ext_flag = 'D'" // เงินหัก
                                + " and d.emp_seq = @EmpSequence"
                                + " group by d.emp_seq ) as sum_expense"
                        + " FROM hrm_pr_payrolls AS payroll "
                        + " Inner Join hrm_pr_payroll_dtls AS payrollD ON payroll.pay_seq = payrollD.pay_seq AND payrollD.record_status not like 'D' "
                        + " Inner Join hrm_ps_employees AS Emp ON Emp.emp_seq = payrollD.emp_seq AND payrollD.record_status not like 'D' "
                        + " Inner Join mas_name_prefixes AS prefitx ON prefitx.name_prefix_seq = Emp.name_prefix_seq AND prefitx.record_status not like 'D' "
                        + " Inner Join hrm_pr_extra_types ON payrollD.ext_seq = hrm_pr_extra_types.ext_seq "
                        + " LEFT JOIN HRM_MP_POSITIONS p ON Emp.POS_SEQ = p.POS_SEQ "
                        + " LEFT JOIN HRM_MP_JOB_SITES wj ON Emp.WORKING_JOB_SITE_SEQ = wj.JOB_SITE_SEQ "
                        + " LEFT JOIN HRM_PS_EMP_BOOK_BANKS AS bk_acc ON bk_acc.emp_seq = Emp.emp_seq AND bk_acc.RECORD_STATUS = 'U'"
                        + " LEFT JOIN FIM_MAS_BANKS acc ON bk_acc.BANK_SEQ = acc.BANK_SEQ "
                        + " LEFT JOIN FIM_MAS_BANK_BRANCHS bb ON bk_acc.BRANCH_SEQ = bb.BRANCH_SEQ"
                        + " WHERE Emp.emp_seq = @EmpSequence "
                            + " and payroll.pay_year = @Year"
                            + " and payroll.pay_month = @Month "
                        + " ORDER BY payroll.pay_month ";
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        //-----------------------

        public DataSet ListPayrollsExtra(int pay_seq, int emp_seq)
        {
            dbManager.CreateParameters(3);
            dbManager.AddParameters(0, "@pay_seq", pay_seq);
            dbManager.AddParameters(1, "@emp_seq", emp_seq);
            dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text
                , "SELECT pd.*, et.EXT_NAME "
                + " FROM HRM_PR_PAYROLL_DTLS pd"
                + " LEFT JOIN HRM_PR_EXTRA_TYPES et ON pd.EXT_SEQ = et.EXT_SEQ"
                + " WHERE pd.PAY_SEQ = @pay_seq"
                    + " AND pd.EMP_SEQ = @emp_seq"
                    + " AND pd.EXT_SEQ not like 99999"
                    + " AND pd.RECORD_STATUS=@record_status");
            dbManager.Dispose();
            return ds;
        }

        public bool ChkDupPayrollsExtra(int pay_seq, int emp_seq, int ext_seq)
        {
            dbManager.Open();
            dbManager.CreateParameters(4);
            dbManager.AddParameters(0, "@pay_seq", pay_seq);
            dbManager.AddParameters(1, "@emp_seq", emp_seq);
            dbManager.AddParameters(2, "@ext_seq", ext_seq);
            dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            
            ds = dbManager.ExecuteDataSet(CommandType.Text
                , "select * from hrm_pr_payroll_dtls "
                + " where pay_seq = @pay_seq"
                + " and ext_seq = @ext_seq"
                + " and emp_seq = @emp_seq"
                + " and record_status = @record_status");
            if (ds.Tables[0].Rows.Count != 0)
                return true;
            else
                return false;
        }

        public void InsertPayrollsExtra(int pay_seq, int emp_seq, int ext_seq, Double ext_amount, string CreatedBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@pay_seq", pay_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@ext_seq", ext_seq);
                dbManager.AddParameters(3, "@ext_amount", ext_amount);
                dbManager.AddParameters(4, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(5, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(6, "@CreatedWhen", DateTime.Today);
                
                dbManager.ExecuteNonQuery(CommandType.Text
                    , "INSERT INTO hrm_pr_payroll_dtls"
                        + "(pay_seq, emp_seq, ext_seq, amount, amount_pay, record_status, created_by, created_when)"
                    + " VALUES (@pay_seq, @emp_seq, @ext_seq, @ext_amount, @ext_amount, @record_status, @CreatedBy,@CreatedWhen)");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        public void UpdatePayrollsExtra(int Sequence, int ext_seq, Double ext_amount, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@ext_seq", ext_seq);
                dbManager.AddParameters(2, "@ext_amount", ext_amount);
                dbManager.AddParameters(3, "@updated_by", UpdateBy);
                dbManager.AddParameters(4, "@updated_when", DateTime.Today);                

                dbManager.ExecuteNonQuery(CommandType.Text
                    , "UPDATE hrm_pr_payroll_dtls"
                        + " SET ext_seq=@ext_seq"
                        + ", amount=@ext_amount"
                        + ", updated_by=@updated_by"
                        + ", updated_when=@updated_when"
                    + " WHERE pay_dtls_seq = @sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public void DeletePayrollsExtra(int Sequence, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_pr_payroll_dtls SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE pay_dtls_seq=@sequence");
                
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        
    }
}
